package za.co.smartmi.dao.impl;

import za.co.smartmi.dao.SystemUserRoleDao;
import za.co.smartmi.model.SystemUserRole;
import za.co.smartmi.model.SystemUserRolePk;
import za.co.smartmi.dao.exceptions.SystemUserRoleDaoException;
import java.util.List;
import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import za.co.smartmi.dao.support.JdbcGenericDao;

@Repository
public class SystemUserRoleDaoImpl extends JdbcGenericDao implements ParameterizedRowMapper<SystemUserRole>, SystemUserRoleDao
{
    
        @Autowired
        public void setDataSource(DataSource dataSource) {
                setJdbcTemplate(new JdbcTemplate(dataSource));
	}
        
	/**
	 * Method 'insert'
	 * 
	 * @param dto
	 * @return SystemUserRolePk
	 */
	@Transactional
	public SystemUserRolePk insert(SystemUserRole dto)
	{
		getJdbcTemplate().update("INSERT INTO " + getTableName() + " ( username, role ) VALUES ( ?, ? )",dto.getUsername(),dto.getRole());
		return dto.createPk();
	}

	/** 
	 * Updates a single row in the system_user_role table.
	 */
	@Transactional
	public void update(SystemUserRolePk pk, SystemUserRole dto) throws SystemUserRoleDaoException
	{
		getJdbcTemplate().update("UPDATE " + getTableName() + " SET username = ?, role = ? WHERE username = ? AND role = ?",dto.getUsername(),dto.getRole(),pk.getUsername(),pk.getRole());
	}

	/** 
	 * Deletes a single row in the system_user_role table.
	 */
	@Transactional
	public void delete(SystemUserRolePk pk) throws SystemUserRoleDaoException
	{
		getJdbcTemplate().update("DELETE FROM " + getTableName() + " WHERE username = ? AND role = ?",pk.getUsername(),pk.getRole());
	}

	/**
	 * Method 'mapRow'
	 * 
	 * @param rs
	 * @param row
	 * @throws SQLException
	 * @return SystemUserRole
	 */
	public SystemUserRole mapRow(ResultSet rs, int row) throws SQLException
	{
		SystemUserRole dto = new SystemUserRole();
		dto.setUsername( rs.getString( 1 ) );
		dto.setRole( rs.getString( 2 ) );
		return dto;
	}

	/**
	 * Method 'getTableName'
	 * 
	 * @return String
	 */
	public String getTableName()
	{
		return "smartMI.system_user_role";
	}

	/** 
	 * Returns all rows from the system_user_role table that match the criteria 'username = :username AND role = :role'.
	 */
	@Transactional
	public SystemUserRole findByPrimaryKey(String username, String role) throws SystemUserRoleDaoException
	{
		try {
			List<SystemUserRole> list = getJdbcTemplate().query("SELECT username, role FROM " + getTableName() + " WHERE username = ? AND role = ?", this,username,role);
			return list.size() == 0 ? null : list.get(0);
		}
		catch (Exception e) {
			throw new SystemUserRoleDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the system_user_role table that match the criteria ''.
	 */
	@Transactional
	public List<SystemUserRole> findAll() throws SystemUserRoleDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT username, role FROM " + getTableName() + " ORDER BY username, role", this);
		}
		catch (Exception e) {
			throw new SystemUserRoleDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the system_user_role table that match the criteria 'username = :username'.
	 */
	@Transactional
	public List<SystemUserRole> findWhereUsernameEquals(String username) throws SystemUserRoleDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT username, role FROM " + getTableName() + " WHERE username = ? ORDER BY username", this,username);
		}
		catch (Exception e) {
			throw new SystemUserRoleDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the system_user_role table that match the criteria 'role = :role'.
	 */
	@Transactional
	public List<SystemUserRole> findWhereRoleEquals(String role) throws SystemUserRoleDaoException
	{
		try {
			return getJdbcTemplate().query("SELECT username, role FROM " + getTableName() + " WHERE role = ? ORDER BY role", this,role);
		}
		catch (Exception e) {
			throw new SystemUserRoleDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns the rows from the system_user_role table that matches the specified primary-key value.
	 */
	public SystemUserRole findByPrimaryKey(SystemUserRolePk pk) throws SystemUserRoleDaoException
	{
		return findByPrimaryKey( pk.getUsername(), pk.getRole() );
	}

}
